﻿using Microsoft.Data.SqlClient;
using System.Data;

namespace StatisticalQuery
{
    public static class Common
    {
        /// <summary>
        /// 根据查询名称创建查询
        /// </summary>
        /// <param name="Connect">数据库连接字符串</param>
        /// <param name="QueryName">存储过程名</param>
        /// <param name="parameters">所有参数值用逗号隔开</param>
        /// <returns></returns>
        public static List<object>? Create(string Connect, string QueryName, string parameters)
        {
            if (QueryName == "WorkLoad")
            {
                var wl = new WorkLoad();
                return wl.GetTable(Connect, QueryName, parameters).GetObjectList();
            }
            return null;
        }
    }

    public sealed class WorkLoad
    {
        public DataTable? table {  get; set; }
    }

    public static class WorkLoadExtension
    {
        //var columm = table.AsEnumerable().Select(row => row.Field<string>("EntryPersonName")).ToArray(); // 获得一列值
        /// <summary>
        /// 获得查询结果集
        /// </summary>
        /// <param name="Connect"></param>
        /// <param name="QueryName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static WorkLoad GetTable(this WorkLoad wl, string Connect, string QueryName, string parameters)
        {
            using (SqlConnection con = new SqlConnection(Connect))  //创建连接字符串
            {
                SqlCommand cmd = new SqlCommand(QueryName, con); //执行数据查询
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StartTime", SqlDbType.NVarChar, 50);
                cmd.Parameters.Add("@EndTime", SqlDbType.NVarChar, 50);

                cmd.Parameters["@StartTime"].Value = parameters.Split(',')[0];
                cmd.Parameters["@EndTime"].Value = parameters.Split(',')[1];

                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable table = new DataTable();
                table.Load(dr);
                cmd.Connection.Close();
                wl.table = table;
                return wl;
            }
        }

        /// <summary>
        /// 查询结果处理
        /// </summary>
        public static List<object> GetObjectList(this WorkLoad wl)
        {
            var list = new List<object>();
            if (wl.table != null)
            {
                foreach (DataRow row in wl.table.Rows)
                {
                    list.Add(new { EntryPersonName = row["EntryPersonName"].ToString(), PCount = row["PCount"].ToString() });
                }
            }
            return list;
        }
    }
}
